package com.example.springboot_test.common.poi.util;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.http.HttpUtil;
import com.example.springboot_test.common.poi.constant.LineStyleConstant;
import com.example.springboot_test.common.poi.constant.POIExcelConstant;
import com.example.springboot_test.common.util.MatchUtil;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressBase;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.main.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTConnector;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTShape;

import java.io.File;
import java.io.FileInputStream;
import java.util.*;
import java.util.stream.Collectors;

import static org.apache.poi.ss.usermodel.CellType.NUMERIC;

/**
 * POI Excel工具类
 *
 * @author xudongmaster
 */
public class POIExcelUtil {
    /**
     * 给Cell添加批注
     *
     * @param cell      单元格
     * @param value     批注内容
     * @param extension 扩展名
     */
    public static void addComment(Cell cell, String value, String extension) {
        Sheet sheet = cell.getSheet();
        cell.removeCellComment();
        if ("xls".equals(extension)) {
            ClientAnchor anchor = new HSSFClientAnchor();
            // 关键修改
            anchor.setDx1(0);
            anchor.setDx2(0);
            anchor.setDy1(0);
            anchor.setDy2(0);
            anchor.setCol1(cell.getColumnIndex());
            anchor.setRow1(cell.getRowIndex());
            anchor.setCol2(cell.getColumnIndex() + 5);
            anchor.setRow2(cell.getRowIndex() + 6);
            // 结束
            Drawing drawing = sheet.getDrawingPatriarch();
            if (drawing == null) {
                drawing = sheet.createDrawingPatriarch();
            }
            Comment comment = drawing.createCellComment(anchor);
            // 输入批注信息
            comment.setString(new HSSFRichTextString(value));
            // 将批注添加到单元格对象中
            cell.setCellComment(comment);
        } else if ("xlsx".equals(extension)) {
            ClientAnchor anchor = new XSSFClientAnchor();
            // 关键修改
            anchor.setDx1(0);
            anchor.setDx2(0);
            anchor.setDy1(0);
            anchor.setDy2(0);
            anchor.setCol1(cell.getColumnIndex());
            anchor.setRow1(cell.getRowIndex());
            anchor.setCol2(cell.getColumnIndex() + 5);
            anchor.setRow2(cell.getRowIndex() + 6);
            // 结束
            Drawing drawing = sheet.getDrawingPatriarch();
            if (drawing == null) {
                drawing = sheet.createDrawingPatriarch();
            }
            Comment comment = drawing.createCellComment(anchor);
            // 输入批注信息
            comment.setString(new XSSFRichTextString(value));
            // 将批注添加到单元格对象中
            cell.setCellComment(comment);
        }
    }

    /**
     * 插入网络图片(单个单元格)
     *
     * @param workbook 文档对象
     * @param sheet    sheet页对象
     * @param url      网络路径
     * @param rowIndex 行号
     * @param colIndex 开始列号
     */
    public static void insertUrlImg(Workbook workbook, Sheet sheet, String url, int rowIndex, int colIndex) {
        insertUrlImg(workbook, sheet, url, rowIndex, rowIndex, colIndex, colIndex);
    }

    /**
     * 插入网络图片
     *
     * @param workbook      文档对象
     * @param sheet         sheet页对象
     * @param url           网络路径
     * @param beginRowIndex 开始行号
     * @param endRowIndex   结束行号
     * @param beginColIndex 开始列号
     * @param endColIndex   结束列号
     */
    public static void insertUrlImg(Workbook workbook, Sheet sheet, String url, int beginRowIndex, int endRowIndex
            , int beginColIndex, int endColIndex) {
        byte[] bytes = HttpUtil.downloadBytes(url);
        insertImg(workbook, sheet, bytes, beginRowIndex, endRowIndex, beginColIndex, endColIndex);
    }

    /**
     * 插入本地图片(单个单元格)
     *
     * @param workbook 文档对象
     * @param sheet    sheet页对象
     * @param url      本地路径
     * @param rowIndex 行号
     * @param colIndex 开始列号
     */
    public static void insertFileImg(Workbook workbook, Sheet sheet, String url, int rowIndex, int colIndex) throws Exception {
        insertFileImg(workbook, sheet, url, rowIndex, rowIndex, colIndex, colIndex);
    }

    /**
     * 插入本地图片
     *
     * @param workbook      文档对象
     * @param sheet         sheet页对象
     * @param url           本地路径
     * @param beginRowIndex 开始行号
     * @param endRowIndex   结束行号
     * @param beginColIndex 开始列号
     * @param endColIndex   结束列号
     */
    public static void insertFileImg(Workbook workbook, Sheet sheet, String url, int beginRowIndex, int endRowIndex
            , int beginColIndex, int endColIndex) throws Exception {
        //将本地路径的图片变成二进制流
        FileInputStream fileInputStream = new FileInputStream(new File(url));
        byte[] bytes = IoUtil.readBytes(fileInputStream);
        fileInputStream.close();
        //插入图片
        insertImg(workbook, sheet, bytes, beginRowIndex, endRowIndex, beginColIndex, endColIndex);
    }

    /**
     * 插入图片
     *
     * @param workbook      文档对象
     * @param sheet         sheet页对象
     * @param picture       图片二进制流数组
     * @param beginRowIndex 开始行号
     * @param endRowIndex   结束行号
     * @param beginColIndex 开始列号
     * @param endColIndex   结束列号
     */
    public static void insertImg(Workbook workbook, Sheet sheet, byte[] picture, int beginRowIndex, int endRowIndex
            , int beginColIndex, int endColIndex) {
        insertImg(workbook, sheet, picture, beginRowIndex, endRowIndex, beginColIndex, endColIndex, Workbook.PICTURE_TYPE_JPEG);
    }

    /**
     * 插入图片
     *
     * @param workbook      文档对象
     * @param sheet         sheet页对象
     * @param picture       图片二进制流数组
     * @param beginRowIndex 开始行号
     * @param endRowIndex   结束行号
     * @param beginColIndex 开始列号
     * @param endColIndex   结束列号
     */
    public static void insertImg(Workbook workbook, Sheet sheet, byte[] picture, int beginRowIndex, int endRowIndex
            , int beginColIndex, int endColIndex, int pictureType) {
        //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
        Drawing drawing = sheet.getDrawingPatriarch();
        if (drawing == null) {
            drawing = sheet.createDrawingPatriarch();
        }
        //anchor主要用于设置图片的属性
        ClientAnchor anchor = null;
        if (workbook instanceof HSSFWorkbook) {
            anchor = new HSSFClientAnchor(0, 0, 255, 255, (short) beginColIndex, beginRowIndex, (short) (endColIndex + 1), endRowIndex + 1);
        } else if (workbook instanceof XSSFWorkbook) {
            anchor = new XSSFClientAnchor(0, 0, 255, 255, (short) beginColIndex, beginRowIndex, (short) (endColIndex + 1), endRowIndex + 1);
        }
        //插入图片
        drawing.createPicture(anchor, workbook.addPicture(picture, pictureType));
    }

    /**
     * 替换Excel模板文件内容
     *
     * @param replaceMap 文档数据
     */
    public static void replaceSheet(Map<String, Object> replaceMap, Workbook workbook, Sheet sheet, Integer endRowIndex) {
        try {
            Iterator rows = sheet.rowIterator();
            while (rows.hasNext()) {
                Row row = (Row) rows.next();
                if (row == null) {
                    continue;
                }
                int num = row.getLastCellNum();
                if (endRowIndex != null && row.getRowNum() > endRowIndex) {
                    break;
                }
                for (int i = 0; i < num; i++) {
                    Cell cell = row.getCell(i);
                    if (cell == null || cell.getCellTypeEnum() == NUMERIC || cell.getStringCellValue() == null) {
                        continue;
                    }
                    String value = cell.getStringCellValue();
                    if (StrUtil.isNotEmpty(value) && value.contains("${")) {
                        cell.setCellValue(replaceValue(value, replaceMap));
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 替换内容 ${}
     *
     * @param value 值
     * @param replaceMap 替换Map
     * @return 值
     */
    public static String replaceValue(String value, Map<String, Object> replaceMap) {
        String str = value.substring(value.indexOf("${") + 2, value.indexOf('}'));
        if (StrUtil.isEmpty(str)) {
            return value;
        }
        value = value.replace("${" + str + "}", (String) replaceMap.get(str));
        return value;
    }

    /**
     * 插入线条(线条颜色默认为黑色，线条样式默认为Solid，线条宽度默认为0.5)
     *
     * @param workbook      文档对象
     * @param sheet         sheet页对象
     * @param beginRowIndex 开始行号
     * @param endRowIndex   结束行号
     * @param beginColIndex 开始列号
     * @param endColIndex   结束列号
     */
    public static void insertLine(Workbook workbook, Sheet sheet, int beginRowIndex, int endRowIndex, int beginColIndex
            , int endColIndex) {
        insertLine(workbook, sheet, beginRowIndex, endRowIndex, beginColIndex, endColIndex, POIExcelUtil.getXSSFRGBColor(0, 0, 0)
                , LineStyleConstant.INT_SOLID, 0.5d);
    }

    /**
     * 插入线条
     *
     * @param workbook      文档对象
     * @param sheet         sheet页对象
     * @param beginRowIndex 开始行号
     * @param endRowIndex   结束行号
     * @param beginColIndex 开始列号
     * @param endColIndex   结束列号
     * @param lineColor     线条颜色
     * @param lineStyle     线条样式
     * @param lineWidth     线条宽度
     */
    public static void insertLine(Workbook workbook, Sheet sheet, int beginRowIndex, int endRowIndex, int beginColIndex, int endColIndex
            , Color lineColor, int lineStyle, double lineWidth) {
        insertShape(workbook, sheet, beginRowIndex, endRowIndex, beginColIndex, endColIndex, lineColor, lineStyle, lineWidth
                , ShapeTypes.LINE);
    }

    /**
     * 插入形状
     *
     * @param workbook      文档对象
     * @param sheet         sheet页对象
     * @param beginRowIndex 开始行号
     * @param endRowIndex   结束行号
     * @param beginColIndex 开始列号
     * @param endColIndex   结束列号
     * @param lineColor     线条颜色
     * @param lineStyle     线条样式
     * @param lineWidth     线条宽度
     * @param shapeType     形状类型
     */
    public static void insertShape(Workbook workbook, Sheet sheet, int beginRowIndex, int endRowIndex, int beginColIndex, int endColIndex
            , Color lineColor, int lineStyle, double lineWidth, int shapeType) {
        CreationHelper helper = workbook.getCreationHelper();
        //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
        Drawing drawing = sheet.getDrawingPatriarch();
        if (drawing == null) {
            drawing = sheet.createDrawingPatriarch();
        }
        ClientAnchor anchor = helper.createClientAnchor();
        // 设置线条的开始位置
        anchor.setCol1(beginColIndex);
        anchor.setRow1(beginRowIndex);
        // 设置线条的结束位置
        anchor.setCol2(endColIndex);
        anchor.setRow2(endRowIndex);
        if (drawing instanceof XSSFDrawing) {
            XSSFSimpleShape shape = ((XSSFDrawing) drawing).createSimpleShape((XSSFClientAnchor) anchor);
            // 设置形状类型
            shape.setShapeType(shapeType);
            // 设置线宽
            shape.setLineWidth(lineWidth);
            // 设置线的风格
            shape.setLineStyle(lineStyle - 1);
            // 设置线的颜色
            XSSFColor xssfColor = (XSSFColor) lineColor;
            byte[] rgbBytes = xssfColor.getRGB();
            if (rgbBytes == null || rgbBytes.length < 3) {
                shape.setLineStyleColor(0, 0, 0);
            } else {
                shape.setLineStyleColor(rgbBytes[0], rgbBytes[1], rgbBytes[2]);
            }
        }
    }

    /**
     * 行复制(多行)
     *
     * @param workbook     文档对象
     * @param sheet        sheet对象
     * @param fromRowIndex 来源行索引
     * @param toRowIndex   目标行索引
     * @param copyRowNum   复制行数
     */
    public static void copyRow(Workbook workbook, Sheet sheet, int fromRowIndex, int toRowIndex, int copyRowNum, boolean insertFlag
            , Integer colNum) {
        for (int i = 0; i < copyRowNum; i++) {
            Row fromRow = sheet.getRow(fromRowIndex + i);
            int currentToRowIndex = toRowIndex + i;
            Row toRow = sheet.getRow(currentToRowIndex);
            if (insertFlag) {
                //复制行超出原有sheet页的最大行数时，不需要移动直接插入
                if (currentToRowIndex <= sheet.getLastRowNum()) {
                    //先移动要插入的行号所在行及之后的行
                    sheet.shiftRows(currentToRowIndex, sheet.getLastRowNum(), 1, true, false);
                    //移动原有的形状位置（需要移动行里面的形状）
                    XSSFDrawing drawing = (XSSFDrawing) sheet.getDrawingPatriarch();
                    if (drawing != null) {
                        List<XSSFShape> shapeList = drawing.getShapes();
                        shapeList = CollUtil.isNotEmpty(shapeList) ? shapeList.stream().filter(x ->
                                x.getAnchor() != null && ((XSSFClientAnchor) x.getAnchor()).getRow1() >= currentToRowIndex).collect(Collectors.toList()) : new ArrayList<>();
                        for (XSSFShape shape : shapeList) {
                            XSSFClientAnchor anchor = (XSSFClientAnchor) shape.getAnchor();
                            anchor.setRow1(anchor.getRow1() + 1);
                            anchor.setRow2(anchor.getRow2() + 1);
                        }
                    }
                }
                //然后再插入行
                toRow = sheet.createRow(currentToRowIndex);
                //设置行高
                toRow.setHeight(fromRow.getHeight());
            }
            for (int colIndex = 0; colIndex < (colNum != null ? colNum : fromRow.getLastCellNum()); colIndex++) {
                Cell tmpCell = fromRow.getCell(colIndex);
                if (tmpCell == null) {
                    tmpCell = fromRow.createCell(colIndex);
                    if (colIndex != 0) {
                        copyCell(workbook, fromRow.createCell(colIndex - 1), tmpCell);
                    }
                }
                Cell newCell = toRow.createCell(colIndex);
                copyCell(workbook, tmpCell, newCell);
            }
        }
        //获取合并单元格
        List<CellRangeAddress> cellRangeAddressList = sheet.getMergedRegions();
        Map<Integer, List<CellRangeAddress>> rowCellRangeAddressMap = CollUtil.isNotEmpty(cellRangeAddressList) ? cellRangeAddressList.stream().collect(Collectors.groupingBy(CellRangeAddressBase::getFirstRow)) : new HashMap<>();
        //获取形状（线条）
        XSSFDrawing drawing = (XSSFDrawing) sheet.getDrawingPatriarch();
        List<XSSFShape> shapeList = new ArrayList<>();
        Map<Integer, List<XSSFShape>> rowShapeMap = new HashMap<>();
        if (drawing != null) {
            shapeList = drawing.getShapes();
            rowShapeMap = shapeList.stream().filter(x -> x.getAnchor() != null)
                    .collect(Collectors.groupingBy(x -> ((XSSFClientAnchor) x.getAnchor()).getRow1()));
        }
        for (int i = 0; i < copyRowNum; i++) {
            Row toRow = sheet.getRow(toRowIndex + i);
            //复制合并单元格
            List<CellRangeAddress> rowCellRangeAddressList = rowCellRangeAddressMap.get(fromRowIndex + i);
            if (CollUtil.isNotEmpty(rowCellRangeAddressList)) {
                for (CellRangeAddress cellRangeAddress : rowCellRangeAddressList) {
                    CellRangeAddress newCellRangeAddress = new CellRangeAddress(toRow.getRowNum(), (toRow.getRowNum() +
                            (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress
                            .getFirstColumn(), cellRangeAddress.getLastColumn());
                    sheet.addMergedRegionUnsafe(newCellRangeAddress);
                }
            }
            //复制形状（线条）
            List<XSSFShape> rowShapeList = rowShapeMap.get(fromRowIndex + i);
            if (CollUtil.isNotEmpty(rowShapeList)) {
                for (XSSFShape shape : rowShapeList) {
                    //复制描点
                    XSSFClientAnchor fromAnchor = (XSSFClientAnchor) shape.getAnchor();
                    XSSFClientAnchor toAnchor = new XSSFClientAnchor();
                    toAnchor.setDx1(fromAnchor.getDx1());
                    toAnchor.setDx2(fromAnchor.getDx2());
                    toAnchor.setDy1(fromAnchor.getDy1());
                    toAnchor.setDy2(fromAnchor.getDy2());
                    toAnchor.setRow1(toRow.getRowNum());
                    toAnchor.setRow2(toRow.getRowNum() + fromAnchor.getRow2() - fromAnchor.getRow1());
                    toAnchor.setCol1(fromAnchor.getCol1());
                    toAnchor.setCol2(fromAnchor.getCol2());
                    //复制形状
                    if (shape instanceof XSSFConnector) {
                        copyXSSFConnector((XSSFConnector) shape, drawing, toAnchor);
                    } else if (shape instanceof XSSFSimpleShape) {
                        copyXSSFSimpleShape((XSSFSimpleShape) shape, drawing, toAnchor);
                    }
                }
            }
        }
    }

    /**
     * 复制XSSFSimpleShape类
     *
     * @param fromShape 来源Shape对象
     * @param drawing   画布对象
     * @param anchor    坐标对象
     * @return XSSFSimpleShape
     */
    public static XSSFSimpleShape copyXSSFSimpleShape(XSSFSimpleShape fromShape, XSSFDrawing drawing, XSSFClientAnchor anchor) {
        XSSFSimpleShape toShape = drawing.createSimpleShape(anchor);
        CTShape ctShape = fromShape.getCTShape();
        CTShapeProperties ctShapeProperties = ctShape.getSpPr();
        CTLineProperties lineProperties = ctShapeProperties.isSetLn() ? ctShapeProperties.getLn() : ctShapeProperties.addNewLn();
        CTPresetLineDashProperties dashStyle = lineProperties.isSetPrstDash() ? lineProperties.getPrstDash() : CTPresetLineDashProperties.Factory.newInstance();
        STPresetLineDashVal.Enum dashStyleEnum = dashStyle.isSetVal() ? dashStyle.getVal() : STPresetLineDashVal.Enum.forInt(1);
        CTSolidColorFillProperties fill = lineProperties.isSetSolidFill() ? lineProperties.getSolidFill() : lineProperties.addNewSolidFill();
        CTSRgbColor rgb = fill.isSetSrgbClr() ? fill.getSrgbClr() : CTSRgbColor.Factory.newInstance();
        // 设置形状类型
        toShape.setShapeType(fromShape.getShapeType());
        // 设置线宽
        toShape.setLineWidth(lineProperties.getW() * 1.0 / Units.EMU_PER_POINT);
        // 设置线的风格
        toShape.setLineStyle(dashStyleEnum.intValue() - 1);
        // 设置线的颜色
        byte[] rgbBytes = rgb.getVal();
        if (rgbBytes == null) {
            toShape.setLineStyleColor(0, 0, 0);
        } else {
            toShape.setLineStyleColor(rgbBytes[0], rgbBytes[1], rgbBytes[2]);
        }
        return toShape;
    }

    /**
     * 复制XSSFConnector类
     *
     * @param fromShape 来源Shape对象
     * @param drawing   画布对象
     * @param anchor    坐标对象
     * @return XSSFConnector
     */
    public static XSSFConnector copyXSSFConnector(XSSFConnector fromShape, XSSFDrawing drawing, XSSFClientAnchor anchor) {
        XSSFConnector toShape = drawing.createConnector(anchor);
        CTConnector ctConnector = fromShape.getCTConnector();
        CTShapeProperties ctShapeProperties = ctConnector.getSpPr();
        CTLineProperties lineProperties = ctShapeProperties.isSetLn() ? ctShapeProperties.getLn() : ctShapeProperties.addNewLn();
        CTPresetLineDashProperties dashStyle = lineProperties.isSetPrstDash() ? lineProperties.getPrstDash() : CTPresetLineDashProperties.Factory.newInstance();
        STPresetLineDashVal.Enum dashStyleEnum = dashStyle.isSetVal() ? dashStyle.getVal() : STPresetLineDashVal.Enum.forInt(1);
        CTSolidColorFillProperties fill = lineProperties.isSetSolidFill() ? lineProperties.getSolidFill() : lineProperties.addNewSolidFill();
        CTSRgbColor rgb = fill.isSetSrgbClr() ? fill.getSrgbClr() : CTSRgbColor.Factory.newInstance();
        // 设置形状类型
        toShape.setShapeType(fromShape.getShapeType());
        // 设置线宽
        toShape.setLineWidth(lineProperties.getW() * 1.0 / Units.EMU_PER_POINT);
        // 设置线的风格
        toShape.setLineStyle(dashStyleEnum.intValue() - 1);
        // 设置线的颜色
        byte[] rgbBytes = rgb.getVal();
        if (rgbBytes == null) {
            toShape.setLineStyleColor(0, 0, 0);
        } else {
            toShape.setLineStyleColor(rgbBytes[0], rgbBytes[1], rgbBytes[2]);
        }
        return toShape;
    }

    /**
     * 复制单元格
     *
     * @param srcCell  来源单元格
     * @param distCell 目标单元格
     */
    public static void copyCell(Workbook workbook, Cell srcCell, Cell distCell) {
        CellStyle newStyle = workbook.createCellStyle();
        // 克隆style
        newStyle.cloneStyleFrom(srcCell.getCellStyle());
        //样式
        distCell.setCellStyle(newStyle);
        //设置内容
        CellType srcCellType = srcCell.getCellTypeEnum();
        distCell.setCellType(srcCellType);
        if (srcCellType == NUMERIC) {
            if (DateUtil.isCellDateFormatted(srcCell)) {
                distCell.setCellValue(srcCell.getDateCellValue());
            } else {
                distCell.setCellValue(srcCell.getNumericCellValue());
            }
        } else if (srcCellType == CellType.STRING) {
            distCell.setCellValue(srcCell.getRichStringCellValue());
        } else if (srcCellType == CellType.BOOLEAN) {
            distCell.setCellValue(srcCell.getBooleanCellValue());
        } else if (srcCellType == CellType.ERROR) {
            distCell.setCellErrorValue(srcCell.getErrorCellValue());
        } else if (srcCellType == CellType.FORMULA) {
            distCell.setCellFormula(srcCell.getCellFormula());
        }
    }

    /**
     * 得到RBG自定义颜色
     *
     * @param redNum   红色数值
     * @param greenNum 绿色数值
     * @param blueNum  蓝色数值
     * @return color对象
     */
    public static XSSFColor getXSSFRGBColor(int redNum, int greenNum, int blueNum) {
        return new XSSFColor(new byte[]{(byte) redNum, (byte) greenNum, (byte) blueNum}, new DefaultIndexedColorMap());
    }

    /**
     * 设置下拉框数据
     *
     * @param workbook         workbook对象
     * @param sheet            sheet页对象
     * @param startRowIndex    开始行索引
     * @param endRowIndex      结束行索引
     * @param startColumnIndex 开始列索引
     * @param endColumnIndex   结束列索引
     * @param spinnerData      下拉框数据
     */
    public static void setSpinnerData(Workbook workbook, Sheet sheet, int startRowIndex, int endRowIndex, int startColumnIndex
            , int endColumnIndex, String[] spinnerData) {
        if (spinnerData == null || spinnerData.length == 0) {
            return;
        }
        if (startRowIndex < POIExcelConstant.ROW_INDEX_MIN) {
            startRowIndex = POIExcelConstant.ROW_INDEX_MIN;
        }
        if (endRowIndex > POIExcelConstant.ROW_INDEX_MAX) {
            endRowIndex = POIExcelConstant.ROW_INDEX_MAX;
        }
        if (startColumnIndex < POIExcelConstant.COL_INDEX_MIN) {
            startColumnIndex = POIExcelConstant.COL_INDEX_MIN;
        }
        int spinnerDataCharCount = 0;
        if (spinnerData.length >= 256) {
            //大于256字符设置下拉框
            setSpinnerDataGt256(workbook, sheet, startRowIndex, endRowIndex, startColumnIndex, endColumnIndex, spinnerData);
            return;
        }
        for (String spinnerStr : spinnerData) {
            int chineseCount = MatchUtil.matchChineseCount(spinnerStr);
            spinnerDataCharCount += chineseCount * 2 + spinnerStr.length() - chineseCount;
            if (spinnerDataCharCount > 256) {
                //大于256字符设置下拉框
                setSpinnerDataGt256(workbook, sheet, startRowIndex, endRowIndex, startColumnIndex, endColumnIndex, spinnerData);
                return;
            }
        }
        //小于等于256字符设置下拉框
        setSpinnerDataLe256(sheet, startRowIndex, endRowIndex, startColumnIndex, endColumnIndex, spinnerData);
    }

    /**
     * 设置下拉框数据（字符数<=256）
     *
     * @param sheet            sheet页对象
     * @param startRowIndex    开始行索引
     * @param endRowIndex      结束行索引
     * @param startColumnIndex 开始列索引
     * @param endColumnIndex   结束列索引
     * @param spinnerData      下拉框数据
     */
    public static void setSpinnerDataLe256(Sheet sheet, int startRowIndex, int endRowIndex, int startColumnIndex
            , int endColumnIndex, String[] spinnerData) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createExplicitListConstraint(spinnerData);
        //设置单元格范围
        CellRangeAddressList addressList = new CellRangeAddressList(startRowIndex, endRowIndex
                , startColumnIndex, endColumnIndex);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        sheet.addValidationData(dataValidation);
    }

    /**
     * 设置下拉框数据（字符数>=256）下拉框数据先写入隐藏Sheet页中，再从隐藏Sheet页读取
     *
     * @param workbook         workbook对象
     * @param sheet            sheet页对象
     * @param startRowIndex    开始行索引
     * @param endRowIndex      结束行索引
     * @param startColumnIndex 开始列索引
     * @param endColumnIndex   结束列索引
     * @param spinnerData      下拉框数据
     */
    public static void setSpinnerDataGt256(Workbook workbook, Sheet sheet, int startRowIndex, int endRowIndex, int startColumnIndex
            , int endColumnIndex, String[] spinnerData) {
        //设置单元格范围
        CellRangeAddressList addressList = new CellRangeAddressList(startRowIndex, endRowIndex
                , startColumnIndex, endColumnIndex);
        String hiddenSheetName = "hiddenSheet" + cn.hutool.core.date.DateUtil.format(new Date(), "yyyyMMddHHmmssSSS");
        Sheet hiddenSheet = workbook.getSheet(hiddenSheetName);
        if (hiddenSheet == null) {
            hiddenSheet = workbook.createSheet(hiddenSheetName);
            //写入下拉数据到新的sheet页中
            for (int i = 0; i < spinnerData.length; i++) {
                Row row = hiddenSheet.createRow(i);
                Cell cell = row.createCell(0);
                cell.setCellValue(spinnerData[i]);
            }
            //将新建的sheet页隐藏掉
            workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheetName), true);
        }
        DataValidationHelper helper = sheet.getDataValidationHelper();
        //获取隐藏sheet页内容
        String strFormula = "=" + hiddenSheetName + "!$A$1:$A$65535";
        DataValidationConstraint constraint = helper.createFormulaListConstraint(strFormula);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        sheet.addValidationData(dataValidation);
    }

    /**
     * 插入水印
     *
     * @param sheet sheet对象
     * @param bytes 水印图片字节数组
     */
    public static void insertWaterRemark(XSSFSheet sheet, byte[] bytes) {
        //水印图片数据关联sheet对象
        XSSFWorkbook workbook = sheet.getWorkbook();
        int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
        String relationId = sheet.addRelation(null, XSSFRelation.IMAGES, workbook.getAllPictures().get(pictureIdx))
                .getRelationship().getId();
        //将水印图片设置为sheet的背景颜色
        sheet.getCTWorksheet().addNewPicture().setId(relationId);
    }
}
